﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;
using System.Collections;

namespace DataLeakCop
{
    class Program
    {
        private static string configureFile = "Configuration.xml";

        // Valid argument value
        private const string ARG_CREATE_SNAPSHOT = "createSnapshot";
        private const string ARG_ANALYSIS = "analysis";
        private const string ARG_DELETE_SNAPSHOT = "deleteSnapshot";

        private const string ARG_DB_NAME = "dbName";
        private const string ARG_DB_PROVIDER = "dbProvider";
        private const string ARG_DB_DATA_SOURCE = "dbDataSource";
        private const string ARG_DB_USER = "dbUser";
        private const string ARG_DB_PASSWORD = "dbPassword";
        private const string ARG_DB_LOGICAL_FILE = "dbLogicalFile";
        private const string ARG_DB_PERSIST_SECURITY_INFO = "dbPersistSecurityInfo";
        private const string ARG_DB_SNAPSHOT_NAME = "dbSnapshotName";
        private const string ARG_DB_SNAPSHOT_FILE = "dbSnapshotFile";

        // "databasename" AND "dburl" is get from registry file
        private const string ARG_DATABASE_NAME = "databaseName";
        private const string ARG_DB_URL = "dbURL";

        // data base info
        private static string dbName = String.Empty;
        private static string dbProvider = String.Empty;
        private static string dbDataSource = String.Empty;
        private static string dbUser = String.Empty;
        private static string dbPassword = String.Empty;
        // Logical name of your current DB, you can find it in the DB properties
        private static string dbLogicalName = String.Empty;
        private static string dbPersistSecurityInfo = String.Empty;
        
        //snapshot info
        private static string dbSnapshotName = String.Empty;
        // File that used to store the snopshot
        private static string dbSnapshotFile = String.Empty;

        // include or exclude model, default to "include"
        private static string incExcModel = "exclude";

        private static ArrayList excludeStartWithTables = new ArrayList();
        private static ArrayList excludeEndWithTables = new ArrayList();

        private static ArrayList excludeStartWithColumns = new ArrayList();
        private static ArrayList excludeEndWithColumns = new ArrayList();
        private static ArrayList excludeContainColumns = new ArrayList();

        static void Main(string[] args)
        {

            try
            {
                // Get configuration file path and db information first
                foreach (String arg in args)
                {
                    if (arg.StartsWith("-f"))
                    {
                        // Configuration file info
                        string confDir = arg.Remove(0, 2);
                        configureFile = confDir + "\\" + configureFile;
                    }
                    else if (arg.StartsWith("-d"))
                    {
                        //DB connection information
                        string dbInfo = arg.Remove(0, 2);
                        Console.WriteLine("Get db info: " + dbInfo);
                        parseDBInformation(dbInfo);
                    }
                }

                // Execute command
                foreach (String arg in args)
                {
                    if (arg.StartsWith("-c"))
                    {
                        // Command information
                        String command = arg.Remove(0, 2);

                        if (ARG_CREATE_SNAPSHOT.Equals(command))
                        {
                            // Create snapshot

                            parseConfiguration(configureFile);

                            if (!createSnapshot(dbName, dbSnapshotName))
                            {
                                break;
                            }
                        }
                        else if (ARG_ANALYSIS.Equals(command))
                        {
                            // Analysis

                            parseConfiguration(configureFile);
                            analysis(dbName, dbSnapshotName);
                        }
                        else if (ARG_DELETE_SNAPSHOT.Equals(command))
                        {
                            // Delete snapshot

                            parseConfiguration(configureFile);
                            if (!deleteSnapshot(dbSnapshotName))
                            {
                                break;
                            }
                        }
                        else
                        {
                            Console.WriteLine("Illegal argument: " + arg);
                            break;
                        }
                    }
                    else if (arg.StartsWith("-f"))
                    {
                        // Configuration file, already done before
                    }
                    else if (arg.StartsWith("-d"))
                    {
                        //DB connection information, already done before
                    }
                    else
                    {
                        Console.WriteLine("Illegal argument: " + arg);
                        break;
                    }
                }
            }
            catch(Exception e)
            {
                Console.WriteLine(e);
            }
            
        }


        /// <summary>
        /// Parse db information from dbInfo
        /// </summary>
        /// <param name="dbInfo"></param>
        private static void parseDBInformation(String dbInfos)
        {
            char[] chSem = {';'};
            string[] dbInfo = dbInfos.Split(chSem);
            Hashtable dbPara = new Hashtable();
            foreach (string arg in dbInfo)
            {
                char[] chEqual = {'='};
                string[] argInfo = arg.Split(chEqual);
                
                if (argInfo.Length < 2)
                {
                    continue;
                }
                
                dbPara.Add(argInfo[0].ToLower(), argInfo[1]);
                
            }

            dbName = (string)dbPara[ARG_DB_NAME.ToLower()];
            dbProvider = (string)dbPara[ARG_DB_PROVIDER.ToLower()];
            dbDataSource = (string)dbPara[ARG_DB_DATA_SOURCE.ToLower()];
            dbUser = (string)dbPara[ARG_DB_USER.ToLower()];
            dbPassword = (string)dbPara[ARG_DB_PASSWORD.ToLower()];
            dbLogicalName = (string)dbPara[ARG_DB_LOGICAL_FILE.ToLower()];
            dbPersistSecurityInfo = (string)dbPara[ARG_DB_PERSIST_SECURITY_INFO.ToLower()];
            dbSnapshotName = (string)dbPara[ARG_DB_SNAPSHOT_NAME.ToLower()];
            dbSnapshotFile = (string)dbPara[ARG_DB_SNAPSHOT_FILE.ToLower()];

            if(dbName == null || dbName == "")
            {
                // Get db name
                dbName = (string)dbPara[ARG_DATABASE_NAME.ToLower()];

                string dbURL = (string)dbPara[ARG_DB_URL.ToLower()];
                Console.WriteLine("Using db url to get data source: " + dbURL);
                string[] url = dbURL.Split(chSem);
                foreach (string urlinfo in url)
                {
                    if(urlinfo.StartsWith("jdbc:sqlserver://"))
                    {
                        // Remove "jdbc:sqlserver://"
                        dbDataSource = urlinfo.Remove(0, 17);
                    }
                }
            }

            if(dbName == null 
                || dbName == ""
                || dbDataSource == null 
                || dbDataSource == "")
            {
                Console.WriteLine("DB name: " + dbName);
                Console.WriteLine("DB source: " + dbDataSource);
                throw new MissingFieldException("db name or data source is missing!!");
            }

            if(dbLogicalName == null || dbLogicalName == "")
            {
                dbLogicalName = dbName;
            }

            if(dbSnapshotName == null || dbSnapshotName == "")
            {
                dbSnapshotName = dbName + "_dataLeak";
            }

            if (dbSnapshotFile == null || dbSnapshotFile == "")
            {
                dbSnapshotFile = "C:\\" + dbSnapshotName + ".ss";
            }
            
            dbName = "\"" + dbName + "\"";
            dbSnapshotName = "\"" + dbSnapshotName + "\"";

            Console.WriteLine("=======================");
            Console.WriteLine("Parsed database info:");
            Console.WriteLine(ARG_DB_NAME + ": " + dbName);
            Console.WriteLine(ARG_DB_PROVIDER + ": " + dbProvider);
            Console.WriteLine(ARG_DB_DATA_SOURCE + ": " + dbDataSource);
            Console.WriteLine(ARG_DB_USER + ": " + dbUser);
            //Console.WriteLine(ARG_DB_PASSWORD + ": " + dbPassword);
            Console.WriteLine(ARG_DB_PASSWORD + ": ******");
            Console.WriteLine(ARG_DB_LOGICAL_FILE + ": " + dbLogicalName);
            Console.WriteLine(ARG_DB_PERSIST_SECURITY_INFO + ": " + dbPersistSecurityInfo);
            Console.WriteLine(ARG_DB_SNAPSHOT_NAME + ": " + dbSnapshotName);
            Console.WriteLine(ARG_DB_SNAPSHOT_FILE + ": " + dbSnapshotFile);
            Console.WriteLine("=======================");

        }


        /// <summary>
        /// Parse the configuration from xml file
        /// </summary>
        /// <param name="xmlFile"></param>
        private static void parseConfiguration(String xmlFile)
        {
            Console.WriteLine("Parsing configuratin: " + xmlFile);

            XmlDocument doc = null;
            XmlElement root = null;
            
            doc = new XmlDocument();
            doc.Load(xmlFile);
            root = doc.DocumentElement;
           
            // Get include/exclude model
            XmlNode modelNode = root.SelectSingleNode("/configuration/inc_exc_model");
            if (modelNode != null)
            {
                incExcModel = modelNode.InnerText;

                //Console.WriteLine("Include/exclude model: " + incExcModel);
            }

            // Get exclude tables of startWith
            XmlNode exclStartWithTablesNodes = root.SelectSingleNode("/configuration/exclude_table/start_with");
            if (exclStartWithTablesNodes != null)
            {
                foreach (XmlNode node in exclStartWithTablesNodes.ChildNodes)
                {
                    if (!String.Empty.Equals(node.InnerText.Trim()))
                    {
                        excludeStartWithTables.Add(node.InnerText);
                    }
                }
            }

            // Get exclude tables of endWith
            XmlNode exclEndWithTablesNodes = root.SelectSingleNode("/configuration/exclude_table/end_with");
            if (exclEndWithTablesNodes != null)
            {
                foreach (XmlNode node in exclEndWithTablesNodes.ChildNodes)
                {
                    if (!String.Empty.Equals(node.InnerText.Trim()))
                    {
                        excludeEndWithTables.Add(node.InnerText);
                    }
                }
            }

            // Get exclude columns of startWith
            XmlNode exclStartWithColumnsNodes = root.SelectSingleNode("/configuration/exclude_column/start_with");
            if (exclStartWithColumnsNodes != null)
            {
                foreach (XmlNode node in exclStartWithColumnsNodes.ChildNodes)
                {
                    if (!String.Empty.Equals(node.InnerText.Trim()))
                    {
                        excludeStartWithColumns.Add(node.InnerText);
                    }
                }
            }

            // Get exclude columns of endWith
            XmlNode exclEndWithColumnsNodes = root.SelectSingleNode("/configuration/exclude_column/end_with");
            if (exclEndWithColumnsNodes != null)
            {
                foreach (XmlNode node in exclEndWithColumnsNodes.ChildNodes)
                {
                    if (!String.Empty.Equals(node.InnerText.Trim()))
                    {
                        excludeEndWithColumns.Add(node.InnerText);
                    }
                }
            }

            // Get exclude columns of contain
            XmlNode exclContainColumnsNodes = root.SelectSingleNode("/configuration/exclude_column/contain");
            if (exclContainColumnsNodes != null)
            {
                foreach (XmlNode node in exclContainColumnsNodes.ChildNodes)
                {
                    if (!String.Empty.Equals(node.InnerText.Trim()))
                    {
                        excludeContainColumns.Add(node.InnerText);
                    }
                }
            }
            
            
        }

        /// <summary>
        /// Create a snapshot of current database
        /// </summary>
        private static bool createSnapshot(String databaseName, String snapshotName)
        {
            Console.WriteLine("Creating snapshot "+ snapshotName +", please wait...");

            String cmdCreateSnapshot = "CREATE DATABASE  "+ snapshotName + " ON "
                                        + "( NAME = '" + dbLogicalName +"'"
                                        +", FILENAME = '" + dbSnapshotFile + "' ) "
                                        + "AS SNAPSHOT OF " + databaseName;

            String connectString = "Provider=" + dbProvider
                                    + ";Data Source=" + dbDataSource
                                    +";Password=" + dbPassword
                                    +";Persist Security Info=" + dbPersistSecurityInfo
                                    +";User ID=" + dbUser
                                    + ";Initial Catalog=" + databaseName;

            DBOperator dbOprDatabase = new DBOperator(connectString);
            bool bUpdateSuccess = dbOprDatabase.Update(cmdCreateSnapshot);
            
            if(bUpdateSuccess)
            {
                Console.WriteLine("Snapshot created!");
                return true;
            }
            else
            {
                Console.WriteLine("Error occured while creating snapshot!!!");
                return false;
            }
        }

        /// <summary>
        /// Delete the snapshot.
        /// </summary>
        private static bool deleteSnapshot(String snapshotName)
        {
            Console.WriteLine("Deleting snapshot " + snapshotName + ", please wait...");

            String cmdDeleteSnapshot = "USE [master] DROP DATABASE " + snapshotName;

            String connectString = "Provider=" + dbProvider
                                    + ";Data Source=" + dbDataSource
                                    + ";Password=" + dbPassword
                                    + ";Persist Security Info=" + dbPersistSecurityInfo
                                    + ";User ID=" + dbUser
                                    + ";Initial Catalog=" + snapshotName;

            DBOperator dbOprDatabase = new DBOperator(connectString);
            bool bUpdateSuccess = dbOprDatabase.Update(cmdDeleteSnapshot);

            if (bUpdateSuccess)
            {
                Console.WriteLine("Snapshot deleted!");
                return true;
            }
            else
            {
                Console.WriteLine("Error occured while deleting snapshot!!!");
                return false;
            }
        }

        /// <summary>
        /// Analysis the data leak
        /// </summary>
        private static void analysis(String databaseName, String snapshotName)
        {
            Console.WriteLine("Analysising, please wait...");

            // Start time
            TimeSpan startTime = new TimeSpan(DateTime.Now.Ticks);
            bool hasDataLeak = false;

            String originalConnectionString = "Provider=" + dbProvider
                                    + ";Data Source=" + dbDataSource
                                    + ";Password=" + dbPassword
                                    + ";Persist Security Info=" + dbPersistSecurityInfo
                                    + ";User ID=" + dbUser
                                    + ";Initial Catalog=" + snapshotName;

            String targetConnectionString = "Provider=" + dbProvider
                                    + ";Data Source=" + dbDataSource
                                    + ";Password=" + dbPassword
                                    + ";Persist Security Info=" + dbPersistSecurityInfo
                                    + ";User ID=" + dbUser
                                    + ";Initial Catalog=" + databaseName;

            Dictionary<String, int> originalDataSet = ReadResultSet(originalConnectionString, snapshotName);
            Dictionary<String, int> targetDataSet = ReadResultSet(targetConnectionString, dbName);
            
            HashSet<String> tablesHasNotifiedDifferences = new HashSet<string>();

            // This cannot find the data leak that targetDataSet have more records than originalDataSet
            foreach (String key in originalDataSet.Keys)
            {
                String tableName = key.Substring(0, key.LastIndexOf('-'));

                // Try one table once detected the differences.
                if (tablesHasNotifiedDifferences.Contains(tableName))
                {
                    continue;
                }

                if (targetDataSet.ContainsKey(key))
                {
                    if (!targetDataSet[key].Equals(originalDataSet[key]))
                    {
                        Console.WriteLine("<" + tableName + "> has differnt data.");
                        tablesHasNotifiedDifferences.Add(tableName);
                        hasDataLeak = true;
                        continue;
                    }
                }
                else
                {
                    Console.WriteLine("<" + tableName + "> has differnt data.");
                    tablesHasNotifiedDifferences.Add(tableName);
                    hasDataLeak = true;
                    continue;
                }
            }

            // This is to find out the data leak that targetDataSet have more records than originalDataSet
            foreach (String key in targetDataSet.Keys)
            {
                String tableName = key.Substring(0, key.LastIndexOf('-'));

                // Try one table once detected the differences.
                if (tablesHasNotifiedDifferences.Contains(tableName))
                {
                    continue;
                }

                if (originalDataSet.ContainsKey(key))
                {
                    if (!originalDataSet[key].Equals(targetDataSet[key]))
                    {
                        Console.WriteLine("<" + tableName + "> has differnt data.");
                        tablesHasNotifiedDifferences.Add(tableName);
                        hasDataLeak = true;
                        continue;
                    }
                }
                else
                {
                    Console.WriteLine("<" + tableName + "> has differnt data.");
                    tablesHasNotifiedDifferences.Add(tableName);
                    hasDataLeak = true;
                    continue;
                }
            }

            TimeSpan endTime = new TimeSpan(DateTime.Now.Ticks);
            TimeSpan ts = endTime.Subtract(startTime).Duration();

            if(!hasDataLeak)
            {
                Console.WriteLine("No data leak was found.");
            }

            string spanTime = ts.Hours.ToString() + " hours " + ts.Minutes.ToString() + " minutes " + ts.Seconds.ToString() + " seconds.";
            Console.WriteLine();
            Console.WriteLine(spanTime);
        }

        /// <summary>
        /// Remove all the records belong to one table once differneces notified.
        /// </summary>
        /// <param name="targetTableDataSet"></param>
        /// <param name="tableName"></param>
        private static void RemoveAllKeysBelongToOneTable(Hashtable targetTableDataSet, String tableName)
        {
            foreach (String key in targetTableDataSet.Keys)
            {
                if (key.StartsWith(tableName))
                {
                    targetTableDataSet.Remove(key);
                }
            }
        }

        /// <summary>
        /// Will have a hashtable for each table, such as adrmst.
        /// It will be looking like this:
        /// key: adrmst-col-001
        /// value: hashcode( adr_id + adr_nam + adrlin1 + adrln2 ...)
        /// Note: for value, it should eliminate all
        ///       1. non-automatic columns
        ///       2. non-date columns
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="DBName"></param>
        /// <returns></returns>
        private static Dictionary<String, int> ReadResultSet(String connectionString, String DBName)
        {
            String strQueryAllTables = "select TABLE_NAME from " + DBName + ".INFORMATION_SCHEMA.TABLES where TABLE_TYPE <> 'VIEW' ";
            DBOperator dbOperator = new DBOperator(connectionString);
            DataTable dtTables = dbOperator.Read(strQueryAllTables);
            Dictionary<String, int> resultSetData = new Dictionary<String, int>();

            foreach (DataRow row in dtTables.Rows)
            {
                if (row["TABLE_NAME"] != DBNull.Value)
                {
                    String tableName = row["TABLE_NAME"].ToString().Trim();
                    
                    // Filter out the tables with too many records and less changes to have changes.
                    if (IsTableShouldBeFiltered(tableName))
                    {
                        continue;
                    }

                    String strQueryColumns = "select COLUMN_NAME, DATA_TYPE from " + DBName + ".INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" + tableName + "'";
                    dbOperator = new DBOperator(connectionString);
                    DataTable dtColumns = dbOperator.Read(strQueryColumns);

                    // Compose the SQL statement from the table based on qualified columns
                    String strQueryData = String.Empty;
                    String orderBy = String.Empty;
                    foreach (DataRow columnRow in dtColumns.Rows)
                    {
                        if (columnRow["COLUMN_NAME"] != DBNull.Value)
                        {
                            String columnName = columnRow["COLUMN_NAME"].ToString().Trim();
                            String dataType = columnRow["DATA_TYPE"].ToString().Trim();

                            if (!IsColumnShouldBeFiltered(columnName))
                            {
                                if (strQueryData.Equals(String.Empty))
                                {
                                    strQueryData += " select " + columnName;

                                    if (!dataType.Contains("text"))
                                    {
                                        orderBy += " order by " + columnName;
                                    }
                                }
                                else
                                {
                                    strQueryData += ", " + columnName;

                                    if (!dataType.Contains("text"))
                                    {
                                        orderBy += ", " + columnName;
                                    }
                                }
                            }
                        }
                    }
                    
                    // Move on to another table if the table has no qualified columns
                    if (strQueryData.Equals(String.Empty))
                    {
                        continue;
                    }

                    strQueryData += " from " + tableName + orderBy;

                    // Query the data and compose the hashtable
                    dbOperator = new DBOperator(connectionString);
                    DataTable dtData = dbOperator.Read(strQueryData);

                    int i = 0;
                    foreach (DataRow dataRow in dtData.Rows)
                    {
                        String dataRowInString = String.Empty;
                        foreach (DataColumn dataColumn in dtData.Columns)
                        {
                            String column = dataColumn.ColumnName;
                            dataRowInString += dataRow[column].ToString().Trim();
                        }

                        int hashcode = dataRowInString.GetHashCode();

                        String key = tableName + "-" + i;
                        if (resultSetData.ContainsKey(key))
                        {
                            resultSetData[key] = hashcode;
                        }
                        else
                        {
                            resultSetData.Add(key, hashcode);
                        }

                        i++;
                    }
                }
            }

            return resultSetData;
        }

        /// <summary>
        /// Should filter out the table of cty_psz and adrmst which has too many data.
        /// In addition, it has less chance to be changed.
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>Return true if we don't need to check this table</returns>
        private static bool IsTableShouldBeFiltered(String tableName)
        {
            
            // Filter start with tables
            foreach (String startWith in excludeStartWithTables)
            {
                if (tableName.StartsWith(startWith))
                {
                    return true;
                }
            }

            // Filter end with tables
            foreach (String endWith in excludeEndWithTables)
            {
                if (tableName.EndsWith(endWith))
                {
                    return true;
                }
            }

            return false;
        }

        /// <summary>
        /// We need to filter some columns (no need to check)
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns>Return true if we don't need to check this column</returns>
        private static bool IsColumnShouldBeFiltered(String columnName)
        {
            
            // Filter start with columns
            foreach (String startWith in excludeStartWithColumns)
            {
                if (columnName.StartsWith(startWith))
                {
                    return true;
                }
            }

            // Filter end with columns
            foreach (String endWith in excludeEndWithColumns)
            {
                if (columnName.EndsWith(endWith))
                {
                    return true;
                }
            }

            // Filter contain columns
            foreach (String contain in excludeContainColumns)
            {
                if (columnName.Contains(contain))
                {
                    return true;
                }
            }

            return false;

        }
    }
}
